package org.sqlproc.engine;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.HashSet;
import java.util.List;

import org.hibernate.Session;
import org.hibernate.Transaction;
import org.junit.Test;
import org.sqlproc.engine.SqlEngine;
import org.sqlproc.engine.SqlOrder;
import org.sqlproc.engine.form.PersonForm;
import org.sqlproc.engine.form.PersonNameForm;
import org.sqlproc.engine.model.Country;
import org.sqlproc.engine.model.Gender;
import org.sqlproc.engine.model.Person;

public class CommonTestBasic extends TestDatabase {

    protected String getDataSetFile() {
        return "dbunit/BasicTest.xml";
    }

    public void testAnsiBasic(boolean newGrammar) {
        Session session = sessionFactory.openSession();
        Transaction tx = session.beginTransaction();

        SqlEngine sqlEngine = getSqlEngine("ANSI_BASIC");

        String sql = sqlEngine.getSql(new Object(), null, SqlEngine.NO_ORDER);
        logger.info(sql);
        assertTrue(sql
                .contains("p.ID, p.NAME_FIRST first, p.NAME_LAST last, p.SSN_NUMBER ssn, p.SSN_COUNTRY country, p.BIRTHDATE, p.SEX, p.CREATEDDATE, p.CREATEDBY, p.LASTUPDATED, p.LASTUPDATEDBY, p.VERSION from PERSON p"));
        assertTrue(sql.contains("order by id"));

        List<Person> list = sqlEngine.query(session, Person.class, new Object());

        assertEquals(2, list.size());
        Person p = list.get(0);
        assertEquals(new Long(2), p.getId());
        assertEquals("1953-05-16", p.getBirthDate().toString());
        assertEquals(null, p.getCreatedDate());
        assertEquals(null, p.getCreatedBy());
        assertEquals("2006-12-08", p.getLastUpdated().toString());
        assertEquals("dbunit", p.getLastUpdatedBy());
        assertEquals(new Long(1), p.getVersion());
        assertEquals(Gender.MALE, p.getSex());
        assertNotNull(p.getSsn());
        assertEquals("123456", p.getSsn().getNumber());
        assertEquals(Country.US, p.getSsn().getCountry());
        assertNotNull(p.getName());
        assertEquals("Pierce", p.getName().getFirst());
        assertEquals("Brosnan", p.getName().getLast());

        tx.commit();
        session.close();
    }

    public void testAnsiSqlExtBasic(boolean newGrammar) {
        Session session = sessionFactory.openSession();
        Transaction tx = session.beginTransaction();

        SqlEngine sqlEngine = getSqlEngine("SQL_EXT_BASIC");

        PersonForm pf = new PersonForm();
        pf.setName(new PersonNameForm());
        pf.getName().setLast("Brosnan");

        PersonForm pfc = new PersonForm();
        pfc.setName(new PersonNameForm());
        pfc.getName().setFirst("Pierce");

        String sql = sqlEngine.getSql(pf, pfc, SqlEngine.NO_ORDER);
        logger.info(sql);
        assertTrue(sql.contains("'pierce' first"));
        assertTrue(sql.contains(":name_last last"));

        List<Person> list = sqlEngine.query(session, Person.class, pf, pfc, 0, 0);

        assertEquals(2, list.size());
        Person p = list.get(0);
        assertEquals(new Long(2), p.getId());
        assertEquals("pierce", p.getName().getFirst());
        assertEquals("BROSNAN", p.getName().getLast());

        tx.commit();
        session.close();
    }

    public void testBasic(boolean newGrammar) {
        Session session = sessionFactory.openSession();
        Transaction tx = session.beginTransaction();

        SqlEngine sqlEngine = getSqlEngine("BASIC");

        String sql = sqlEngine.getSql(new Object(), null, SqlEngine.ASC_ORDER);
        logger.info(sql);
        assertTrue(sql
                .contains("p.ID, p.NAME_FIRST first, p.NAME_LAST last, p.SSN_NUMBER ssn, p.SSN_COUNTRY country, p.BIRTHDATE, p.SEX, p.CREATEDDATE, p.CREATEDBY, p.LASTUPDATED, p.LASTUPDATEDBY, p.VERSION from PERSON p"));
        assertTrue(sql.contains("order by id ASC"));

        List<Person> list = sqlEngine.query(session, Person.class, new Object(), null, SqlEngine.ASC_ORDER, 0, 0, 0);

        assertEquals(2, list.size());
        Person p = list.get(0);
        assertEquals(new Long(2), p.getId());
        assertEquals("1953-05-16", p.getBirthDate().toString());
        assertEquals(null, p.getCreatedDate());
        assertEquals(null, p.getCreatedBy());
        assertEquals("2006-12-08", p.getLastUpdated().toString());
        assertEquals("dbunit", p.getLastUpdatedBy());
        assertEquals(new Long(1), p.getVersion());
        assertEquals(Gender.MALE, p.getSex());
        assertNotNull(p.getSsn());
        assertEquals("123456", p.getSsn().getNumber());
        assertEquals(Country.US, p.getSsn().getCountry());
        assertNotNull(p.getName());
        assertEquals("Pierce", p.getName().getFirst());
        assertEquals("Brosnan", p.getName().getLast());

        tx.commit();
        session.close();
    }

    public void testFormBasic(boolean newGrammar) {
        Session session = sessionFactory.openSession();
        Transaction tx = session.beginTransaction();

        SqlEngine sqlEngine = getSqlEngine("FORM_BASIC");
        DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        PersonForm pf = new PersonForm();
        pf.setId(2L);
        pf.setName(new PersonNameForm());
        pf.getName().setFirst("Pierce");
        // pf.getName().setLast("Brosnan");
        // pf.setSsn(new Ssn());
        // pf.getSsn().setNumber("123456");
        // pf.getSsn().setCountry("us");
        // try { pf.setBirthDate(sdf.parse("1953-05-16 00:00:00")); } catch
        // (Exception ex) {fail();}
        pf.setSex(Gender.MALE);
        pf.setLastUpdatedBy("dbunit");
        try {
            pf.setLastUpdated(sdf.parse("2006-12-08 00:00:00"));
        } catch (Exception ex) {
            fail();
        }
        pf.setVersion(1L);

        String sql = sqlEngine.getSql(pf, null, SqlEngine.ASC_ORDER);
        logger.info(sql);
        assertTrue(sql.contains("AND  p.id ="));
        assertTrue(sql.contains("AND  p.NAME_FIRST ="));
        // assertTrue(sql.contains("AND  p.NAME_LAST ="));
        // assertTrue(sql.contains("AND  p.SSN_NUMBER ="));
        // assertTrue(sql.contains("p.SSN_COUNTRY ="));
        assertTrue(sql.contains("AND  p.SEX ="));
        // assertTrue(sql.contains("AND  p.LASTUPDATED ="));
        assertTrue(sql.contains("AND  p.LASTUPDATEDBY ="));
        // assertTrue(sql.contains("AND  p.BIRTHDATE ="));
        assertTrue(sql.contains("AND  p.VERSION ="));
        assertTrue(sql.contains("order by id ASC"));

        List<Person> list = sqlEngine.query(session, Person.class, pf, null, SqlEngine.ASC_ORDER, 0, 0, 0);

        assertEquals(1, list.size());
        Person p = list.get(0);
        assertEquals(new Long(2), p.getId());
        assertEquals("1953-05-16", p.getBirthDate().toString());
        assertEquals(null, p.getCreatedDate());
        assertEquals(null, p.getCreatedBy());
        assertEquals("2006-12-08 00:00:00", sdf.format(p.getLastUpdated()));
        assertEquals("dbunit", p.getLastUpdatedBy());
        assertEquals(new Long(1), p.getVersion());
        assertEquals(Gender.MALE, p.getSex());
        assertNotNull(p.getSsn());
        assertEquals("123456", p.getSsn().getNumber());
        assertEquals(Country.US, p.getSsn().getCountry());
        assertNotNull(p.getName());
        assertEquals("Pierce", p.getName().getFirst());
        assertEquals("Brosnan", p.getName().getLast());

        tx.commit();
        session.close();
    }

    public void testFormOrBasic(boolean newGrammar) {
        Session session = sessionFactory.openSession();
        Transaction tx = session.beginTransaction();

        SqlEngine sqlEngine = getSqlEngine("FORM_OR_BASIC");
        DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        PersonForm pf = new PersonForm();
        pf.setId(2L);
        pf.setName(new PersonNameForm());
        pf.getName().setFirst("Jan");
        // pf.getName().setLast("Brosnan");
        // pf.setSsn(new Ssn());
        // pf.getSsn().setNumber("123456");
        // pf.getSsn().setCountry("us");
        // try { pf.setBirthDate(sdf.parse("1953-05-16 00:00:00")); } catch
        // (Exception ex) {fail();}
        pf.setSex(Gender.MALE);
        pf.setLastUpdatedBy("dbunit");
        try {
            pf.setLastUpdated(sdf.parse("2006-12-08 00:00:00"));
        } catch (Exception ex) {
            fail();
        }
        pf.setVersion(1L);

        String sql = sqlEngine.getSql(pf, null, SqlEngine.ASC_ORDER);
        logger.info(sql);
        assertTrue(sql.contains("AND  p.id ="));
        assertTrue(sql.contains("OR  p.NAME_FIRST ="));
        // assertTrue(sql.contains("AND  p.NAME_LAST ="));
        // assertTrue(sql.contains("AND  p.SSN_NUMBER ="));
        // assertTrue(sql.contains("p.SSN_COUNTRY ="));
        assertTrue(sql.contains("OR  p.SEX ="));
        // assertTrue(sql.contains("AND  p.LASTUPDATED ="));
        assertTrue(sql.contains("OR  p.LASTUPDATEDBY ="));
        // assertTrue(sql.contains("AND  p.BIRTHDATE ="));
        // assertTrue(sql.contains("AND  p.VERSION ="));
        assertTrue(sql.contains("order by id ASC"));

        List<Person> list = sqlEngine.query(session, Person.class, pf, null, SqlEngine.ASC_ORDER, 0, 0, 0);

        assertEquals(2, list.size());
        Person p = list.get(0);
        assertEquals(new Long(2), p.getId());
        assertEquals("1953-05-16", p.getBirthDate().toString());
        assertEquals(null, p.getCreatedDate());
        assertEquals(null, p.getCreatedBy());
        assertEquals("2006-12-08 00:00:00", sdf.format(p.getLastUpdated()));
        assertEquals("dbunit", p.getLastUpdatedBy());
        assertEquals(new Long(1), p.getVersion());
        assertEquals(Gender.MALE, p.getSex());
        assertNotNull(p.getSsn());
        assertEquals("123456", p.getSsn().getNumber());
        assertEquals(Country.US, p.getSsn().getCountry());
        assertNotNull(p.getName());
        assertEquals("Pierce", p.getName().getFirst());
        assertEquals("Brosnan", p.getName().getLast());

        tx.commit();
        session.close();
    }

    public void testConstantBasic(boolean newGrammar) {
        Session session = sessionFactory.openSession();
        Transaction tx = session.beginTransaction();

        SqlEngine sqlEngine = getSqlEngine("CONSTANT_BASIC");
        DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        PersonForm pf = new PersonForm();
        pf.setId(2L);
        pf.setName(new PersonNameForm());
        pf.getName().setFirst("Pierce");
        // pf.getName().setLast("Brosnan");
        // pf.setSsn(new Ssn());
        // pf.getSsn().setNumber("123456");
        // pf.getSsn().setCountry("us");
        // try { pf.setBirthDate(sdf.parse("1953-05-16 00:00:00")); } catch
        // (Exception ex) {fail();}
        pf.setSex(Gender.MALE);
        pf.setLastUpdatedBy("dbunit");
        try {
            pf.setLastUpdated(sdf.parse("2006-12-08 00:00:00"));
        } catch (Exception ex) {
            fail();
        }
        pf.setVersion(1L);

        String sql = sqlEngine.getSql(new Object(), pf, SqlEngine.ASC_ORDER);
        logger.info(sql);
        assertTrue(sql.contains("AND  p.id ="));
        assertTrue(sql.contains("AND  p.NAME_FIRST ="));
        // assertTrue(sql.contains("AND  p.NAME_LAST ="));
        // assertTrue(sql.contains("AND  p.SSN_NUMBER ="));
        // assertTrue(sql.contains("p.SSN_COUNTRY ="));
        assertTrue(sql.contains("AND  p.SEX ="));
        // assertTrue(sql.contains("AND  p.LASTUPDATED ="));
        assertTrue(sql.contains("AND  p.LASTUPDATEDBY ="));
        // assertTrue(sql.contains("AND  p.BIRTHDATE ="));
        assertTrue(sql.contains("AND  p.VERSION ="));
        assertTrue(sql.contains("order by id ASC"));

        List<Person> list = sqlEngine.query(session, Person.class, new Object(), pf, SqlEngine.ASC_ORDER, 0, 0, 0);

        assertEquals(1, list.size());
        Person p = list.get(0);
        assertEquals(new Long(2), p.getId());
        assertEquals("1953-05-16", p.getBirthDate().toString());
        assertEquals(null, p.getCreatedDate());
        assertEquals(null, p.getCreatedBy());
        assertEquals("2006-12-08 00:00:00", sdf.format(p.getLastUpdated()));
        assertEquals("dbunit", p.getLastUpdatedBy());
        assertEquals(new Long(1), p.getVersion());
        assertEquals(Gender.MALE, p.getSex());
        assertNotNull(p.getSsn());
        assertEquals("123456", p.getSsn().getNumber());
        assertEquals(Country.US, p.getSsn().getCountry());
        assertNotNull(p.getName());
        assertEquals("Pierce", p.getName().getFirst());
        assertEquals("Brosnan", p.getName().getLast());

        tx.commit();
        session.close();
    }

    public void testConstantOrBasic(boolean newGrammar) {
        Session session = sessionFactory.openSession();
        Transaction tx = session.beginTransaction();

        SqlEngine sqlEngine = getSqlEngine("CONSTANT_OR_BASIC");
        DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        PersonForm pf = new PersonForm();
        pf.setId(2L);
        pf.setName(new PersonNameForm());
        pf.getName().setFirst("Jan");
        // pf.getName().setLast("Brosnan");
        // pf.setSsn(new Ssn());
        // pf.getSsn().setNumber("123456");
        // pf.getSsn().setCountry("us");
        // try { pf.setBirthDate(sdf.parse("1953-05-16 00:00:00")); } catch
        // (Exception ex) {fail();}
        pf.setSex(Gender.MALE);
        pf.setLastUpdatedBy("dbunit");
        try {
            pf.setLastUpdated(sdf.parse("2006-12-08 00:00:00"));
        } catch (Exception ex) {
            fail();
        }
        pf.setVersion(1L);

        String sql = sqlEngine.getSql(new Object(), pf, SqlEngine.ASC_ORDER);
        logger.info(sql);
        assertTrue(sql.contains("AND  p.id ="));
        assertTrue(sql.contains("OR  p.NAME_FIRST ="));
        // assertTrue(sql.contains("AND  p.NAME_LAST ="));
        // assertTrue(sql.contains("AND  p.SSN_NUMBER ="));
        // assertTrue(sql.contains("p.SSN_COUNTRY ="));
        assertTrue(sql.contains("OR  p.SEX ="));
        // assertTrue(sql.contains("AND  p.LASTUPDATED ="));
        assertTrue(sql.contains("OR  p.LASTUPDATEDBY ="));
        // assertTrue(sql.contains("AND  p.BIRTHDATE ="));
        // assertTrue(sql.contains("AND  p.VERSION ="));
        assertTrue(sql.contains("order by id ASC"));

        List<Person> list = sqlEngine.query(session, Person.class, new Object(), pf, SqlEngine.ASC_ORDER, 0, 0, 0);

        assertEquals(2, list.size());
        Person p = list.get(0);
        assertEquals(new Long(2), p.getId());
        assertEquals("1953-05-16", p.getBirthDate().toString());
        assertEquals(null, p.getCreatedDate());
        assertEquals(null, p.getCreatedBy());
        assertEquals("2006-12-08 00:00:00", sdf.format(p.getLastUpdated()));
        assertEquals("dbunit", p.getLastUpdatedBy());
        assertEquals(new Long(1), p.getVersion());
        assertEquals(Gender.MALE, p.getSex());
        assertNotNull(p.getSsn());
        assertEquals("123456", p.getSsn().getNumber());
        assertEquals(Country.US, p.getSsn().getCountry());
        assertNotNull(p.getName());
        assertEquals("Pierce", p.getName().getFirst());
        assertEquals("Brosnan", p.getName().getLast());

        tx.commit();
        session.close();
    }

    public void testConstantInAndOrderBasic(boolean newGrammar) {
        Session session = sessionFactory.openSession();
        Transaction tx = session.beginTransaction();

        SqlEngine sqlEngine = getSqlEngine("CONSTANT_IN_ORDER_BASIC");

        PersonForm pf = new PersonForm();
        pf.setIdSet(new HashSet<Long>());
        pf.getIdSet().add(1L);
        pf.getIdSet().add(2L);

        String sql = sqlEngine.getSql(new Object(), pf, SqlOrder.getAscOrder(2));
        logger.info(sql);
        assertTrue(sql.contains("AND  p.id in (1,2)"));
        assertTrue(sql.contains("order by NAME_FIRST ASC"));

        List<Person> list = sqlEngine.query(session, Person.class, new Object(), pf, SqlOrder.getAscOrder(2), 0, 0, 0);

        assertEquals(1, list.size());
        Person p = list.get(0);
        assertEquals(new Long(2), p.getId());

        tx.commit();
        session.close();
    }

    public void testIdentifierAndCOnstantOutOfMetaSqlBasic(boolean newGrammar) {
        Session session = sessionFactory.openSession();
        Transaction tx = session.beginTransaction();

        SqlEngine sqlEngine = getSqlEngine("INDENT_AND_CONSTANT_OUT_OF_META_SQL_BASIC");

        PersonForm pf = new PersonForm();
        pf.setName(new PersonNameForm());
        pf.getName().setLast("Brosnan");

        PersonForm pfc = new PersonForm();
        pfc.setName(new PersonNameForm());
        pfc.getName().setFirst("Pierce");

        String sql = sqlEngine.getSql(pf, pfc, SqlEngine.ASC_ORDER);
        logger.info(sql);
        assertTrue(sql.contains("'Pierce' first"));
        assertTrue(sql.contains(":name_last last"));
        assertTrue(sql.contains("order by id ASC"));

        List<Person> list = sqlEngine.query(session, Person.class, pf, pfc, 0, 0);

        assertEquals(2, list.size());
        Person p = list.get(0);
        assertEquals(new Long(2), p.getId());
        assertEquals("Pierce", p.getName().getFirst());
        assertEquals("Brosnan", p.getName().getLast());

        tx.commit();
        session.close();
    }

    @Test
    public void testSqlInBracesBasic(boolean newGrammar) {

        Session session = sessionFactory.openSession();
        Transaction tx = session.beginTransaction();

        SqlEngine sqlEngine = getSqlEngine("SQL_IN_BRACES_BASIC");

        PersonForm pf = new PersonForm();
        pf.setName(new PersonNameForm());
        pf.getName().setLast("Brosnan");

        PersonForm pfc = new PersonForm();
        pfc.setName(new PersonNameForm());
        pfc.getName().setFirst("Pierce");

        String sql = sqlEngine.getSql(pf, pfc, SqlEngine.NO_ORDER);
        logger.info(sql);
        assertTrue(sql.contains("'Pierce' first"));
        assertTrue(sql.contains(":name_last last"));

        List<Person> list = sqlEngine.query(session, Person.class, pf, pfc, 0, 0);

        assertEquals(2, list.size());
        Person p = list.get(0);
        assertEquals(new Long(2), p.getId());
        assertEquals("Pierce", p.getName().getFirst());
        assertEquals("BROSNAN", p.getName().getLast());

        sql = sqlEngine.getSql(new Object(), null, SqlEngine.NO_ORDER);
        logger.info(sql);
        assertFalse(sql.contains("'Pierce' first"));
        assertFalse(sql.contains(":name_last last"));

        list = sqlEngine.query(session, Person.class, new Object(), null, 0, 0);

        assertEquals(2, list.size());
        p = list.get(0);
        assertEquals(new Long(2), p.getId());
        assertEquals("", p.getName().getFirst());
        assertEquals("", p.getName().getLast());

        tx.commit();
        session.close();
    }

    public void testNullFormBasic(boolean newGrammar) {
        Session session = sessionFactory.openSession();
        Transaction tx = session.beginTransaction();

        SqlEngine sqlEngine = getSqlEngine("ANSI_BASIC");

        String sql = sqlEngine.getSql(null, null, SqlEngine.NO_ORDER);
        logger.info(sql);
        assertTrue(sql
                .contains("p.ID, p.NAME_FIRST first, p.NAME_LAST last, p.SSN_NUMBER ssn, p.SSN_COUNTRY country, p.BIRTHDATE, p.SEX, p.CREATEDDATE, p.CREATEDBY, p.LASTUPDATED, p.LASTUPDATEDBY, p.VERSION from PERSON p"));
        assertTrue(sql.contains("order by id"));

        List<Person> list = sqlEngine.query(session, Person.class, null);

        assertEquals(2, list.size());
        Person p = list.get(0);
        assertEquals(new Long(2), p.getId());
        assertEquals("1953-05-16", p.getBirthDate().toString());
        assertEquals(null, p.getCreatedDate());
        assertEquals(null, p.getCreatedBy());
        assertEquals("2006-12-08", p.getLastUpdated().toString());
        assertEquals("dbunit", p.getLastUpdatedBy());
        assertEquals(new Long(1), p.getVersion());
        assertEquals(Gender.MALE, p.getSex());
        assertNotNull(p.getSsn());
        assertEquals("123456", p.getSsn().getNumber());
        assertEquals(Country.US, p.getSsn().getCountry());
        assertNotNull(p.getName());
        assertEquals("Pierce", p.getName().getFirst());
        assertEquals("Brosnan", p.getName().getLast());

        tx.commit();
        session.close();
    }
}
